package com.navi.loaddata.loadService.impl.PanelData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.MinMaxEvttimeStamp;
import com.navi.loaddata.core.gpEntity.YearMonth;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.LoggerUtil;
import org.postgresql.util.PSQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;


/**
 * @author fuchenyang
 * @date 2018/4/19
 */
@Service
public class LoadPanelData_CService {
    private static final Logger logger = LoggerFactory.getLogger(LoadPanelData_CService.class);

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Autowired
    private LoadPanelDataBean loadPanelDataBean;


    public String[] loadPanelDataMain(EtlConfDModel etlConfDModel, EtlConfD etlConfD) throws Exception {
        String[] resMsg = loadPanelData(etlConfDModel, etlConfD);
        String msg = etlConfDModel.getOpe();
        if (org.apache.commons.lang.StringUtils.isEmpty(etlConfDModel.getOpe())) msg = "ALL";
        logger.info("loadPanelData_C "+  "wpp_"+ etlConfDModel.getOpe() +"_cpanel_sum" +" finish!", msg);
        return resMsg;
    }

    private String[] loadPanelData(EtlConfDModel etlConfDModel, EtlConfD etlConfD) throws Exception {
        String[] resMsg = new String[3];
        int okCnt = 0;
        int errCnt = 0;
        String errMsg = null;

        //找到需要插入数据的表
//        String tmpTab = "wpp_"+ etlConfDModel.getOpe() +"_cpanel_sum";
        String tmpTab = "wpp_cdefect_panel_sum";

//        //判断表是否已存在，若不存在，则将数据插入到wpp_default_fpanel_sum表中
//        List<String> tabList = getTabList(etlConfDModel);
//
//        if(tabList.size() == 0 || tabList == null) {
//            tmpTab = "wpp_default_cpanel_sum";
//        }

        String panelShop = "LoadPanelData_C";

        List<YearMonth> yearMonthList = loadPanelDataBean.getYearMonth(etlConfDModel, tmpTab);
        List<YearMonth> yearMonthNewList = new ArrayList<>();

        //panel表中查不到对应的日期，则说明没有对应数据，返回为空
        if (yearMonthList.size() == 0) {
            resMsg[0] = "0";
            resMsg[1] = "0";
            resMsg[2] = "查询时间" + etlConfDModel.getRunStartTimestamp() + "到" + etlConfDModel.getRunEndTimestamp() + "内，无符合Glass命名规则的glass_id";

            return resMsg;
        } else {
            //去除不符合规则的数据，若yearMonthList还有数据则继续后续逻辑，若yearMonthList无数据，则返回
            for (YearMonth yearMonth : yearMonthList) {
                if (yearMonth.getMonth() != null && yearMonth.getYear() != null) {
                    if (yearMonth.getYear().matches("[0-9]{2,}") && (yearMonth.getMonth().matches("[1-9]") || yearMonth.getMonth().matches("[A-C]"))) {
                        yearMonthNewList.add(yearMonth);
                    }
                }
            }

            if (yearMonthNewList.size() == 0) {
                resMsg[0] = "0";
                resMsg[1] = "0";
                resMsg[2] = "查询时间" + etlConfDModel.getRunStartTimestamp() + "到" + etlConfDModel.getRunEndTimestamp() + "内，无符合Glass命名规则的glass_id";

                return resMsg;
            }
        }

        //获取by db_timestamp时间段内的最小和最大的evt_timestamp，作为panel查询时间段
        List<MinMaxEvttimeStamp> minMaxEvttimeStamp = loadPanelDataBean.getMinMaxEvtTime(etlConfDModel, tmpTab);

        String EvtStartTime = "";
        String EvtEndTime = "";
        String MaxEndTime = "";
        Boolean loopFlag = false;

        if (minMaxEvttimeStamp.get(0).getMinTime() == null || minMaxEvttimeStamp.get(0).getMaxTime() == null) {
            resMsg[0] = "0";
            resMsg[1] = "0";
            resMsg[2] = "查询时间" + etlConfDModel.getRunStartTimestamp() + "到" + etlConfDModel.getRunEndTimestamp() + "内，evt_timestamp为空";

            return resMsg;
        } else {
            SimpleDateFormat sqldate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date minTime = new Date();
            Date maxTime = new Date();
            Date limitMinTime = new Date();

            try {
                minTime = sqldate.parse(minMaxEvttimeStamp.get(0).getMinTime());
                maxTime = sqldate.parse(minMaxEvttimeStamp.get(0).getMaxTime());
                limitMinTime = sqldate.parse("2018-03-01 00:00:00");
            } catch (ParseException e) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
                e.printStackTrace();
            }

            //若查询时间段最小evt_timestamp小于2018-03-01 00:00:00，则最小时间用2018-03-01 00:00:00，否则就用查询获取的最小时间
            if (limitMinTime.before(minTime)) {
                EvtStartTime = minMaxEvttimeStamp.get(0).getMinTime();
            } else {
                EvtStartTime = "2018-03-01 00:00:00";
            }

            try {
                minTime = sqldate.parse(EvtStartTime);
            } catch (ParseException e) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
                e.printStackTrace();
            }

            //若起始时间距离结束时间大于一个月，则将时间按月分割进行查询及数据删除插入操作
            while ((maxTime.getTime() - minTime.getTime())/(24*60*60*1000) > 30) {
                loopFlag = true;

                Calendar calStart = Calendar.getInstance();
                calStart.setTime(minTime);
                calStart.add(Calendar.MONTH, 1);
                calStart.set(Calendar.DAY_OF_MONTH, 1);
                EvtStartTime = sqldate.format(minTime);
                EvtEndTime = sqldate.format(calStart.getTime());
                MaxEndTime = sqldate.format(maxTime);

//                //删除要跑时间段内的数据
//                loadPanelDataBean.deletePeriodData(etlConfDModel, tmpTab, panelShop， EvtStartTime, EvtEndTime);

                try {
                    //删除Panel Summary表中存在的重复数据（By OpeId和PanelId）
                    if (yearMonthNewList.size() > 0) {
                        loadPanelDataBean.deleteDuplicateData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthNewList);
                    } else {
                        loadPanelDataBean.deleteDuplicateData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthList);
                    }

                    //向Panel Summary表中插入数据
                    loadPanelDataBean.insertData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, okCnt, loopFlag);
                } catch (Exception e) {
                    String errorMsg = e.toString();
                    if (errorMsg.indexOf("GenericJDBCException") > -1) {
                        if (e.getCause().toString().indexOf("deadlock") > -1) {
                            etlConfD.setRunFlg("N");
                            etlConfD.setExt2("N");
                            etlConfD.setRunEndTimestamp(null);
                            loadPanelDataBean.updateEtlConfD(etlConfD);

                            resMsg[0] = "0";
                            resMsg[1] = "0";
                            resMsg[2] = e.getCause().toString();

                            return resMsg;
                        } else {
                            logger.error("数据库insert/delete出现GenericJDBCException报错", e);
                            throw e;
                        }
                    } else {
                        logger.error("数据库insert/delete出现报错", e);
                        throw e;
                    }
                }

                try {
                    minTime = sqldate.parse(EvtEndTime);
                } catch (ParseException e) {
                    logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
                    e.printStackTrace();
                }
            }

            //若已经进入by month循环当中，则进行最后一次数据删除插入操作，若没有，则直接用最小时间作为起始时间进行数据删除插入操作
            if (loopFlag) {
                EvtStartTime = sqldate.format(minTime);
                EvtEndTime = minMaxEvttimeStamp.get(0).getMaxTime();
                loopFlag = false;

                try {
                    //删除Panel Summary表中存在的重复数据（By OpeId和PanelId）
                    if (yearMonthNewList.size() > 0) {
                        loadPanelDataBean.deleteDuplicateData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthNewList);
                    } else {
                        loadPanelDataBean.deleteDuplicateData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthList);
                    }

                    //向Panel Summary表中插入数据
                    loadPanelDataBean.insertData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, okCnt, loopFlag);
                } catch (Exception e) {
                    String errorMsg = e.toString();
                    if (errorMsg.indexOf("GenericJDBCException") > -1) {
                        if (e.getCause().toString().indexOf("deadlock") > -1) {
                            etlConfD.setRunFlg("N");
                            etlConfD.setExt2("N");
                            etlConfD.setRunEndTimestamp(null);
                            loadPanelDataBean.updateEtlConfD(etlConfD);

                            resMsg[0] = "0";
                            resMsg[1] = "0";
                            resMsg[2] = e.getCause().toString();

                            return resMsg;
                        } else {
                            logger.error("数据库insert/delete出现GenericJDBCException报错", e);
                            throw e;
                        }
                    } else {
                        logger.error("数据库insert/delete出现报错", e);
                        throw e;
                    }
                }
            } else {
                EvtEndTime = minMaxEvttimeStamp.get(0).getMaxTime();

                try {
                    //删除Panel Summary表中存在的重复数据（By OpeId和PanelId）
                    if (yearMonthNewList.size() > 0) {
                        loadPanelDataBean.deleteDuplicateData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthNewList);
                    } else {
                        loadPanelDataBean.deleteDuplicateData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, errCnt, loopFlag, MaxEndTime, yearMonthList);
                    }

                    //向Panel Summary表中插入数据
                    loadPanelDataBean.insertData(etlConfDModel, tmpTab, panelShop, EvtStartTime, EvtEndTime, errMsg, okCnt, loopFlag);
                } catch (Exception e) {
                    String errorMsg = e.toString();
                    if (errorMsg.indexOf("GenericJDBCException") > -1) {
                        if (e.getCause().toString().indexOf("deadlock") > -1) {
                            etlConfD.setRunFlg("N");
                            etlConfD.setExt2("N");
                            etlConfD.setRunEndTimestamp(null);
                            loadPanelDataBean.updateEtlConfD(etlConfD);

                            resMsg[0] = "0";
                            resMsg[1] = "0";
                            resMsg[2] = e.getCause().toString();

                            return resMsg;
                        } else {
                            logger.error("数据库insert/delete出现GenericJDBCException报错", e);
                            throw e;
                        }
                    } else {
                        logger.error("数据库insert/delete出现报错", e);
                        throw e;
                    }
                }
            }
        }

        resMsg[0] = String.valueOf(okCnt);
        resMsg[1] = String.valueOf(errCnt);
        resMsg[2] = errMsg;

        return resMsg;
    }


    public List<String> getTabList(EtlConfDModel etlConfDModel) throws ETLException2 {
        List<String> tabList = new ArrayList<>();

        StringBuffer sqlTabDef = new StringBuffer();

//        sqlTabDef.append("select table_name from information_schema.columns ");
//        sqlTabDef.append(" where table_schema='sor'");
//        sqlTabDef.append(" and table_name = 'wpp_"+ etlConfDModel.getOpe().toLowerCase() +"_cpanel_sum'");
        sqlTabDef.append("select cast(tablename as varchar) from pg_catalog.pg_tables ");
        sqlTabDef.append(" where tablename = 'wpp_"+ etlConfDModel.getOpe().toLowerCase() +"_cpanel_sum'");

        try {
            logger.info("tabList SQL:" + sqlTabDef.toString());

            Date startTime = new Date();

            tabList = gpDao.findBySQL(sqlTabDef.toString()); //查看是否有对应的表

            Date endTime = new Date();
            long durableTime = (endTime.getTime() - startTime.getTime())/1000;
            logger.info("查tabList耗时:" + durableTime +"s");
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException2(ErrorCode.ERROR_GET_GP_DEFECT_TAB, e);
        }

        return tabList;
    }
}
